package manager;

import entities.Department;
import entities.EmployeeDepartmentHistory;
import entities.Holiday;
import entities.Patient;
import entities.PatientMaterial;
import entities.PropertySys;
import entities.Shift;
import entities.ShiftRequest;
import entities.User;
import entities.ViewPatientMaterial;
import entities.ViewScheduler;
import entities.ViewShiftRequest;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DataManager {

    private PropertySys sysProperties;
    private String username;
    private String password;
    private boolean connected = false;
    private boolean login = false;
    private User user;
    private int indexObject = 0;
    private Connection con;

    public DataManager() throws ClassNotFoundException {
        init();
    }

    private void init() throws ClassNotFoundException {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        loadProperties();
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public boolean isConnected() {
        return connected;
    }

    public void setConnected(boolean connected) {
        this.connected = connected;
    }

    public boolean isLogin() {
        return login;
    }

    public void setLogin(boolean login) {
        this.login = login;
    }

    public PropertySys getSysProperties() {
        return sysProperties;
    }

    public void setSysProperties(PropertySys sysProperties) {
        this.sysProperties = sysProperties;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public int getIndexObject() {
        return indexObject;
    }

    public Object getDriver() {
        switch (indexObject) {
            case 1:
                return new ManagerDriver(this);
            case 2:
                return new AdminSysDriver(this);
            case 3:
                return new AdminBusiDriver(this);
            case 4:
                return new DepartmentHeadDriver(this);
            case 5:
                return new DoctorDriver(this);
            default:
                return null;
        }
    }

    public GenericDriver getGenericDriver() {
        return new GenericDriver(this);
    }

    public void loadProperties() {
        sysProperties = new PropertySys();
        Properties properties = new Properties();
        String filePath = PropertySys.getPropertyFilePath();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
            properties.load(fis);
            sysProperties.setServerName(properties.getProperty("ServerName"));
            sysProperties.setLogin(properties.getProperty("Login"));
            sysProperties.setDSP(properties.getProperty("DSP"));
            sysProperties.setDatabaseName(properties.getProperty("DatabaseName"));
            int port = 0;
            try {
                port = Integer.parseInt(properties.getProperty("ServerPort"));
            } catch (Exception e) {
            }
            sysProperties.setServerPort(port);
            int i = 0;
            try {
                i = Integer.parseInt(properties.getProperty("isRemember"));
            } catch (Exception e) {
            }
            if (i == 1) {
                sysProperties.setRemember(true);
                sysProperties.setUsername(properties.getProperty("Username"));
                sysProperties.setPassword(properties.getProperty("Password"));
            } else {
                sysProperties.setRemember(false);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public boolean connect() {
        if (!connected) {
            try {
                con = DriverManager.getConnection("jdbc:sqlserver://" + sysProperties.getServerName()
                        + ";Database=" + sysProperties.getDatabaseName(),
                        sysProperties.getLogin(), sysProperties.getDSP());
                connected = true;
                return true;
            } catch (SQLException ex) {
                connected = false;
            }
        }
        return false;
    }

    public boolean disConnect() {
        if (connected) {
            try {
                con.close();
                con = null;
                connected = false;
                indexObject = 0;
                return true;
            } catch (SQLException ex) {
                Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }

    public void setChangeProperties() {
        if (connected) {
            String filePath = PropertySys.getPropertyFilePath();
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(filePath);
                Properties properties = new Properties();
                properties.setProperty("ServerName", sysProperties.getServerName());
                properties.setProperty("Login", sysProperties.getLogin());
                properties.setProperty("DSP", sysProperties.getDSP());
                properties.setProperty("DatabaseName", sysProperties.getDatabaseName());
                properties.setProperty("ServerPort", sysProperties.getServerPort() + "");
                if (sysProperties.isRemember()) {
                    properties.setProperty("isRemember", "1");
                    properties.setProperty("Username", username);
                    properties.setProperty("Password", password);
                } else {
                    properties.setProperty("isRemember", "0");
                    properties.setProperty("Username", "");
                    properties.setProperty("Password", "");
                }
                properties.store(fos, "");
            } catch (IOException ex) {
                Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                if (fos != null) {
                    try {
                        fos.close();
                    } catch (IOException ex) {
                        Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
        }
    }

    public void setChangeConfig() {
        String filePath = PropertySys.getPropertyFilePath();
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filePath);
            Properties properties = new Properties();
            properties.setProperty("ServerName", sysProperties.getServerName());
            properties.setProperty("Login", sysProperties.getLogin());
            properties.setProperty("DSP", sysProperties.getDSP());
            properties.setProperty("DatabaseName", sysProperties.getDatabaseName());
            properties.setProperty("ServerPort", sysProperties.getServerPort() + "");
            properties.store(fos, "");
        } catch (IOException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    public boolean login() {
        if (connected) {
            CallableStatement cStmt = null;
            try {
                cStmt = con.prepareCall("{call UserLogin(?, ?)}");
                cStmt.setString(1, username);
                cStmt.setString(2, password);
                ResultSet rs = cStmt.executeQuery();
                if (rs.next()) {
                    user = new User();
                    user.setUserID(rs.getInt(1));
                    user.setLoginID(rs.getString(2));
                    user.setLoginPass(rs.getString(3));
                    user.setTitle(rs.getString(4));
                    int role = rs.getInt(5);
                    int level = rs.getInt(6);
                    user.setRole(role);
                    user.setLevel(level);
                    user.setName(rs.getString(7));
                    user.setMale(rs.getBoolean(8));
                    user.setBirthDate(rs.getDate(9));
                    user.setAddress(rs.getString(10));
                    user.setPhone(rs.getString(11));
                    user.setEmailAddress(rs.getString(12));
                    user.setHireDate(rs.getDate(13));
                    user.setModifiedDate(rs.getDate(14));
                    int did = rs.getInt(15);
                    user.setDepartmentID(did);
                    user.setDepartmentName(rs.getString(16));
                    if (role == 1) {
                        indexObject = 1;
                    } else {
                        if (role == 2) {
                            indexObject = 2;
                        } else {
                            if (role == 3) {
                                indexObject = 4;
                            } else {
                                if (did == 3) {
                                    indexObject = 3;
                                } else {
                                    indexObject = 5;
                                }
                            }
                        }
                    }
                    login = true;
                    return true;
                }
            } catch (Exception e) {
                //
            } finally {
                if (cStmt != null) {
                    try {
                        cStmt.close();
                    } catch (SQLException ex) {
                        Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
        }
        return false;
    }

    public boolean logout() {
        if (login) {
            user = null;
            indexObject = 0;
            return true;
        }
        return false;
    }

    public ArrayList<User> showUser() {
        ArrayList<User> list = new ArrayList<User>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowUser()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setUserID(rs.getInt(1));
                u.setLoginID(rs.getString(2));
                u.setLoginPass(rs.getString(3));
                u.setStatus(rs.getInt(4));
                u.setTitle(rs.getString(5));
                u.setRole(rs.getInt(6));
                u.setLevel(rs.getInt(7));
                u.setName(rs.getString(8));
                u.setMale(rs.getBoolean(9));
                u.setBirthDate(new Date(rs.getDate(10).getTime()));
                u.setAddress(rs.getString(11));
                u.setPhone(rs.getString(12));
                u.setEmailAddress(rs.getString(13));
                u.setHireDate(new Date(rs.getDate(14).getTime()));
                u.setModifiedDate(new Date(rs.getDate(15).getTime()));
                u.setDepartmentID(rs.getInt(16));
                u.setDepartmentName(rs.getString(17));
                list.add(u);
            }
        } catch (SQLException ex) {
            //
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<User> showHead() {
        ArrayList<User> list = new ArrayList<User>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowUser()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                int role = rs.getInt(6);
                if (role == 3) {
                    User u = new User();
                    u.setUserID(rs.getInt(1));
                    u.setLoginID(rs.getString(2));
                    u.setLoginPass(rs.getString(3));
                    u.setStatus(rs.getInt(4));
                    u.setTitle(rs.getString(5));
                    u.setRole(role);
                    u.setLevel(rs.getInt(7));
                    u.setName(rs.getString(8));
                    u.setMale(rs.getBoolean(9));
                    u.setBirthDate(new Date(rs.getDate(10).getTime()));
                    u.setAddress(rs.getString(11));
                    u.setPhone(rs.getString(12));
                    u.setEmailAddress(rs.getString(13));
                    u.setHireDate(new Date(rs.getDate(14).getTime()));
                    u.setModifiedDate(new Date(rs.getDate(15).getTime()));
                    u.setDepartmentID(rs.getInt(16));
                    u.setDepartmentName(rs.getString(17));
                    list.add(u);
                }
            }
        } catch (SQLException ex) {
            //
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<User> showEmployee() {
        ArrayList<User> list = new ArrayList<User>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowUser()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                int role = rs.getInt(6);
                if (role != 1) {
                    User u = new User();
                    u.setUserID(rs.getInt(1));
                    u.setLoginID(rs.getString(2));
                    u.setLoginPass(rs.getString(3));
                    u.setStatus(rs.getInt(4));
                    u.setTitle(rs.getString(5));
                    u.setRole(role);
                    u.setLevel(rs.getInt(7));
                    u.setName(rs.getString(8));
                    u.setMale(rs.getBoolean(9));
                    u.setBirthDate(new Date(rs.getDate(10).getTime()));
                    u.setAddress(rs.getString(11));
                    u.setPhone(rs.getString(12));
                    u.setEmailAddress(rs.getString(13));
                    u.setHireDate(new Date(rs.getDate(14).getTime()));
                    u.setModifiedDate(new Date(rs.getDate(15).getTime()));
                    u.setDepartmentID(rs.getInt(16));
                    u.setDepartmentName(rs.getString(17));
                    list.add(u);
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<User> showEmployeeByDID(int did) {
        ArrayList<User> list = new ArrayList<User>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowEmployeeByDID(?)}");
            cStmt.setInt(1, did);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {

                User u = new User();
                u.setUserID(rs.getInt(1));
                u.setLoginID(rs.getString(2));
                u.setLoginPass(rs.getString(3));
                u.setStatus(rs.getInt(4));
                u.setTitle(rs.getString(5));
                u.setRole(rs.getInt(6));
                u.setLevel(rs.getInt(7));
                u.setName(rs.getString(8));
                u.setMale(rs.getBoolean(9));
                u.setBirthDate(new Date(rs.getDate(10).getTime()));
                u.setAddress(rs.getString(11));
                u.setPhone(rs.getString(12));
                u.setEmailAddress(rs.getString(13));
                u.setHireDate(new Date(rs.getDate(14).getTime()));
                u.setModifiedDate(new Date(rs.getDate(15).getTime()));
                u.setDepartmentID(rs.getInt(16));
                u.setDepartmentName(rs.getString(17));
                list.add(u);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return list;
    }

    public User showEmployeeByID(int id) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowUser()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                int eid = rs.getInt(1);
                if (eid == id) {
                    User u = new User();
                    u.setUserID(eid);
                    u.setLoginID(rs.getString(2));
                    u.setLoginPass(rs.getString(3));
                    u.setStatus(rs.getInt(4));
                    u.setTitle(rs.getString(5));
                    u.setRole(rs.getInt(6));
                    u.setLevel(rs.getInt(7));
                    u.setName(rs.getString(8));
                    u.setMale(rs.getBoolean(9));
                    u.setBirthDate(new Date(rs.getDate(10).getTime()));
                    u.setAddress(rs.getString(11));
                    u.setPhone(rs.getString(12));
                    u.setEmailAddress(rs.getString(13));
                    u.setHireDate(new Date(rs.getDate(14).getTime()));
                    u.setModifiedDate(new Date(rs.getDate(15).getTime()));
                    u.setDepartmentID(rs.getInt(16));
                    u.setDepartmentName(rs.getString(17));
                    return u;
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return null;
    }

    public ArrayList<User> showDoctor() {
        ArrayList<User> list = new ArrayList<User>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowUser()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                int role = rs.getInt(6);
                int level = rs.getInt(7);
                if (role == 4 && level == 1) {
                    User u = new User();
                    u.setUserID(rs.getInt(1));
                    u.setLoginID(rs.getString(2));
                    u.setLoginPass(rs.getString(3));
                    u.setStatus(rs.getInt(4));
                    u.setTitle(rs.getString(5));
                    u.setRole(role);
                    u.setLevel(level);
                    u.setName(rs.getString(8));
                    u.setMale(rs.getBoolean(9));
                    u.setBirthDate(new Date(rs.getDate(10).getTime()));
                    u.setAddress(rs.getString(11));
                    u.setPhone(rs.getString(12));
                    u.setEmailAddress(rs.getString(13));
                    u.setHireDate(new Date(rs.getDate(14).getTime()));
                    u.setModifiedDate(new Date(rs.getDate(15).getTime()));
                    u.setDepartmentID(rs.getInt(16));
                    u.setDepartmentName(rs.getString(17));
                    list.add(u);
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<User> showUserByDID(int departmentID) {
        ArrayList<User> list = new ArrayList<User>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowUserByDepartment(?)}");
            cStmt.setInt(1, departmentID);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                User u = new User();
                u.setUserID(rs.getInt(1));
                u.setLoginID(rs.getString(2));
                u.setLoginPass(rs.getString(3));
                u.setTitle(rs.getString(4));
                u.setRole(rs.getInt(5));
                u.setLevel(rs.getInt(6));
                u.setName(rs.getString(7));
                u.setMale(rs.getBoolean(8));
                u.setBirthDate(new Date(rs.getDate(9).getTime()));
                u.setAddress(rs.getString(10));
                u.setPhone(rs.getString(11));
                u.setEmailAddress(rs.getString(12));
                u.setHireDate(new Date(rs.getDate(13).getTime()));
                u.setModifiedDate(new Date(rs.getDate(14).getTime()));
                u.setDepartmentID(rs.getInt(15));
                u.setDepartmentName(rs.getString(16));
                list.add(u);
            }
        } catch (SQLException ex) {
            //
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public boolean insertUser(User user) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call InsertUser(?,?,?,?,?,?,?,?,?,?,?,?)}");
            cStmt.registerOutParameter(1, Types.INTEGER);
            cStmt.setString(2, user.getLoginID());
            cStmt.setString(3, user.getLoginPass());
            cStmt.setInt(4, user.getDepartmentID());
            cStmt.setInt(5, user.getRole());
            cStmt.setInt(6, user.getLevel());
            cStmt.setString(7, user.getName());
            cStmt.setBoolean(8, user.isMale());
            cStmt.setDate(9, new java.sql.Date(user.getBirthDate().getTime()));
            cStmt.setString(10, user.getAddress());
            cStmt.setString(11, user.getPhone());
            cStmt.setString(12, user.getEmailAddress());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                int id = cStmt.getInt(1);
                user.setUserID(id);
                return true;
            }
        } catch (SQLException ex) {
            //
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean updateUser(User user) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call UpdateUser(?,?,?,?,?,?,?,?,?,?,?,?)}");
            cStmt.setInt(1, user.getUserID());
            cStmt.setString(2, user.getLoginPass());
            cStmt.setInt(3, user.getDepartmentID());
            cStmt.setInt(4, user.getStatus());
            cStmt.setInt(5, user.getRole());
            cStmt.setInt(6, user.getLevel());
            cStmt.setString(7, user.getName());
            cStmt.setBoolean(8, user.isMale());
            cStmt.setDate(9, new java.sql.Date(user.getBirthDate().getTime()));
            cStmt.setString(10, user.getAddress());
            cStmt.setString(11, user.getPhone());
            cStmt.setString(12, user.getEmailAddress());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (SQLException ex) {
            //
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean setLogin(int uid, String pass, int status) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call SetLogin(?, ?, ?)}");
            cStmt.setInt(1, uid);
            cStmt.setString(2, pass);
            cStmt.setInt(3, status);
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean updateProfile(int id, String title, String phone, String emailAddress, String address) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call updateProfile(?, ?, ?, ?, ?)}");
            cStmt.setInt(1, id);
            cStmt.setString(2, title);
            cStmt.setString(3, phone);
            cStmt.setString(4, emailAddress);
            cStmt.setString(5, address);
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean changePass(int id, String oldPass, String newPass) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call changePass(?, ?, ?)}");
            cStmt.setInt(1, id);
            cStmt.setString(2, oldPass);
            cStmt.setString(3, newPass);
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean deleteUser(User user) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call DeleteUser(?)}");
            cStmt.setInt(1, user.getUserID());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public ArrayList<Department> showDepartment() {
        ArrayList<Department> list = new ArrayList<Department>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowDepartment()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                Department department = new Department();
                department.setDepartmentID(rs.getInt(1));
                department.setName(rs.getString(2));
                department.setLocation(rs.getString(3));
                department.setPhone(rs.getString(4));
                department.setDescription(rs.getString(5));
                department.setModifiedDate(rs.getDate(6));
                list.add(department);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<Department> showDepartmentByID(int departmentID) {
        ArrayList<Department> list = new ArrayList<Department>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowDepartmentByID(?)}");
            cStmt.setInt(1, departmentID);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                Department department = new Department();
                department.setDepartmentID(rs.getInt(1));
                department.setName(rs.getString(2));
                department.setLocation(rs.getString(3));
                department.setPhone(rs.getString(4));
                department.setDescription(rs.getString(5));
                department.setModifiedDate(rs.getDate(6));
                list.add(department);
            }
        } catch (SQLException ex) {
            //
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public boolean insertDepartment(Department department) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call InsertDepartment(?,?,?,?,?)}");
            cStmt.registerOutParameter(1, Types.INTEGER);
            cStmt.setString(2, department.getName());
            cStmt.setString(3, department.getLocation());
            cStmt.setString(4, department.getPhone());
            cStmt.setString(5, department.getDescription());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                int id = cStmt.getInt(1);
                department.setDepartmentID(id);
                return true;
            }
        } catch (SQLException ex) {
            //
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return false;
    }

    public boolean updateDepartment(Department department) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call UpdateDepartment(?,?,?,?,?)}");
            cStmt.setInt(1, department.getDepartmentID());
            cStmt.setString(2, department.getName());
            cStmt.setString(3, department.getLocation());
            cStmt.setString(4, department.getPhone());
            cStmt.setString(5, department.getDescription());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean deleteDepartment(Department department) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call DeleteDepartment(?)}");
            cStmt.setInt(1, department.getDepartmentID());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return false;
    }

    public ArrayList<Patient> showPatient() {
        ArrayList<Patient> list = new ArrayList<Patient>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowPatient}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                Patient patient = new Patient();
                patient.setPatientID(rs.getInt(1));
                patient.setIdentityCard(rs.getString(2));
                patient.setName(rs.getString(3));
                patient.setMale(rs.getBoolean(4));
                patient.setBirthDate(rs.getDate(5));
                patient.setAddress(rs.getString(6));
                patient.setPhone(rs.getString(7));
                patient.setModifiedDate(rs.getDate(8));
                list.add(patient);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return list;
    }

    public Patient showPatientByIDC(String idc) {
        ArrayList<Patient> list = new ArrayList<Patient>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowPatient}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                Patient patient = new Patient();
                patient.setPatientID(rs.getInt(1));
                patient.setIdentityCard(rs.getString(2));
                patient.setName(rs.getString(3));
                patient.setMale(rs.getBoolean(4));
                patient.setBirthDate(rs.getDate(5));
                patient.setAddress(rs.getString(6));
                patient.setPhone(rs.getString(7));
                patient.setModifiedDate(rs.getDate(8));
                list.add(patient);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        for (Patient p : list) {
            if (p.getIdentityCard().equals(idc)) {
                return p;
            }
        }
        return null;
    }

    public boolean insertPatient(Patient patient) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call InsertPatient(?,?,?,?,?,?,?)}");
            cStmt.registerOutParameter(1, Types.INTEGER);
            cStmt.setString(2, patient.getIdentityCard());
            cStmt.setString(3, patient.getName());
            cStmt.setBoolean(4, patient.isMale());
            cStmt.setDate(5, new java.sql.Date(patient.getBirthDate().getTime()));
            cStmt.setString(6, patient.getAddress());
            cStmt.setString(7, patient.getPhone());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                int id = cStmt.getInt(1);
                patient.setPatientID(id);
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean updatePatient(Patient patient) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call UpdatePatient(?,?,?,?,?,?,?)}");
            cStmt.setInt(1, patient.getPatientID());
            cStmt.setString(2, patient.getIdentityCard());
            cStmt.setString(3, patient.getName());
            cStmt.setBoolean(4, patient.isMale());
            cStmt.setDate(5, new java.sql.Date(patient.getBirthDate().getTime()));
            cStmt.setString(6, patient.getAddress());
            cStmt.setString(7, patient.getPhone());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean deletePatient(Patient patient) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call DeletePatient(?)}");
            cStmt.setInt(1, patient.getPatientID());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (SQLException ex) {
            //
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public ArrayList<ViewPatientMaterial> showViewPMaterial() {
        ArrayList<ViewPatientMaterial> list = new ArrayList<ViewPatientMaterial>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowInfoPatientMaterial()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewPatientMaterial record = new ViewPatientMaterial();
                record.setPatientID(rs.getInt(1));
                record.setDoctorID(rs.getInt(2));
                record.setStartDate(rs.getDate(3));
                record.setEndDate(rs.getDate(4));
                record.setReason(rs.getString(5));
                record.setEmployeeID(rs.getString(6));
                record.setDoctorName(rs.getString(7));
                record.setDepartmentName(rs.getString(8));
                record.setDoctorPhone(rs.getString(9));
                record.setIdentityCard(rs.getString(10));
                record.setPatientName(rs.getString(11));
                record.setPatientPhone(rs.getString(12));
                list.add(record);
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewPatientMaterial> showViewPatientMaterialByEID(int did) {
        ArrayList<ViewPatientMaterial> list = new ArrayList<ViewPatientMaterial>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowViewPatientMaterialByEID(?)}");
            cStmt.setInt(1, did);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewPatientMaterial record = new ViewPatientMaterial();
                record.setPatientID(rs.getInt(1));
                record.setDoctorID(rs.getInt(2));
                record.setStartDate(rs.getDate(3));
                record.setEndDate(rs.getDate(4));
                record.setReason(rs.getString(5));
                record.setEmployeeID(rs.getString(6));
                record.setDoctorName(rs.getString(7));
                record.setDepartmentName(rs.getString(8));
                record.setDoctorPhone(rs.getString(9));
                record.setIdentityCard(rs.getString(10));
                record.setPatientName(rs.getString(11));
                record.setPatientPhone(rs.getString(12));
                list.add(record);
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ViewPatientMaterial showViewPMaterialByPK(int pid, int did, Date date) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowInfoPatientMaterial()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                int patientID = rs.getInt(1);
                int doctorID = rs.getInt(2);
                Date startDate = rs.getDate(3);
                if (patientID == pid && doctorID == did
                        && (startDate.getDay() == date.getDay() && startDate.getMonth() == date.getMonth() && startDate.getYear() == date.getYear())) {
                    ViewPatientMaterial record = new ViewPatientMaterial();
                    record.setPatientID(rs.getInt(1));
                    record.setDoctorID(rs.getInt(2));
                    record.setStartDate(rs.getDate(3));
                    record.setEndDate(rs.getDate(4));
                    record.setReason(rs.getString(5));
                    record.setEmployeeID(rs.getString(6));
                    record.setDoctorName(rs.getString(7));
                    record.setDepartmentName(rs.getString(8));
                    record.setDoctorPhone(rs.getString(9));
                    record.setIdentityCard(rs.getString(10));
                    record.setPatientName(rs.getString(11));
                    record.setPatientPhone(rs.getString(12));
                    return record;
                }
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return null;
    }

    public ArrayList<PatientMaterial> showPatientMaterial() {
        ArrayList<PatientMaterial> list = new ArrayList<PatientMaterial>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowPatientMaterial()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                PatientMaterial patientMaterial = new PatientMaterial();
                patientMaterial.setPatientID(rs.getInt(1));
                patientMaterial.setEmployeeID(rs.getInt(2));
                patientMaterial.setStartDate(rs.getDate(3));
                patientMaterial.setEndDate(rs.getDate(4));
                patientMaterial.setReason(rs.getString(5));
                list.add(patientMaterial);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<PatientMaterial> showPatientMaterialByEID(int patientMaterialID) {
        ArrayList<PatientMaterial> list = new ArrayList<PatientMaterial>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowPatientMaterialByEID(?)}");
            cStmt.setInt(1, patientMaterialID);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                PatientMaterial patientMaterial = new PatientMaterial();
                patientMaterial.setPatientID(rs.getInt(1));
                patientMaterial.setEmployeeID(rs.getInt(2));
                patientMaterial.setStartDate(rs.getDate(3));
                patientMaterial.setEndDate(rs.getDate(4));
                patientMaterial.setReason(rs.getString(5));
                list.add(patientMaterial);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public boolean insertPatientMaterial(PatientMaterial patientMaterial) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call InsertPatientMaterial(?,?,?)}");
            cStmt.setInt(1, patientMaterial.getPatientID());
            cStmt.setInt(2, patientMaterial.getEmployeeID());
            cStmt.setDate(3, new java.sql.Date(patientMaterial.getStartDate().getTime()));
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean updatePatientMaterial(PatientMaterial patientMaterial) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call UpdatePatientMaterial(?,?,?,?,?)}");
            cStmt.setInt(1, patientMaterial.getPatientID());
            cStmt.setInt(2, patientMaterial.getEmployeeID());
            cStmt.setDate(3, new java.sql.Date(patientMaterial.getStartDate().getTime()));
            cStmt.setDate(4, new java.sql.Date(patientMaterial.getEndDate().getTime()));
            cStmt.setString(5, patientMaterial.getReason());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean deletePatientMaterial(PatientMaterial patientMaterial) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call DeletePatientMaterial(?,?,?)}");
            cStmt.setInt(1, patientMaterial.getPatientID());
            cStmt.setInt(2, patientMaterial.getEmployeeID());
            cStmt.setDate(3, (java.sql.Date) patientMaterial.getStartDate());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public ArrayList<Shift> showShift() {
        ArrayList<Shift> list = new ArrayList<Shift>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowShift()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                Shift shift = new Shift();
                shift.setShiftID(rs.getInt(1));
                shift.setName(rs.getString(2));
                shift.setStartTime(rs.getTime(3));
                shift.setEndTime(rs.getTime(4));
                shift.setModifiedDate(rs.getDate(5));
                list.add(shift);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public Shift showShiftByID(int id) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowShift()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                int shiftID = rs.getInt(1);
                if (shiftID == id) {
                    Shift shift = new Shift();
                    shift.setShiftID(rs.getInt(1));
                    shift.setName(rs.getString(2));
                    shift.setStartTime(rs.getTime(3));
                    shift.setEndTime(rs.getTime(4));
                    shift.setModifiedDate(rs.getDate(5));
                    return shift;


                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return null;
    }

    public boolean insertShift(Shift shift) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call InsertShift(?,?,?,?)}");
            cStmt.setInt(1, shift.getShiftID());
            cStmt.setString(2, shift.getName());
            cStmt.setTime(3, shift.getStartTime());
            cStmt.setTime(4, shift.getEndTime());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean updateShift(Shift shift) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call UpdateShift(?,?,?,?)}");
            cStmt.setInt(1, shift.getShiftID());
            cStmt.setString(2, shift.getName());
            cStmt.setTime(3, shift.getStartTime());
            cStmt.setTime(4, shift.getEndTime());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean deleteShift(Shift shift) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call DeleteShift(?)}");
            cStmt.setInt(1, shift.getShiftID());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public ArrayList<ViewShiftRequest> showSRByRole(int role) {
        ArrayList<ViewShiftRequest> list = new ArrayList<ViewShiftRequest>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowSRByRole(?)}");
            cStmt.setInt(1, role);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewShiftRequest view = new ViewShiftRequest();
                view.setShiftRequestID(rs.getInt(1));
                view.setEmployeeID(rs.getInt(2));
                view.setCreateDate(rs.getDate(3));
                view.setStartDate(rs.getDate(4));
                view.setEndDate(rs.getDate(5));
                view.setShiftId(rs.getInt(6));
                view.setShiftName(rs.getString(7));
                view.setReason(rs.getString(8));
                view.setStatus(rs.getInt(9));
                view.setModifiedDate(rs.getDate(10));
                view.setEmployeeName(rs.getString(11));
                view.setLevel(rs.getInt(12));
                view.setDepartmentName(rs.getString(13));
                list.add(view);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewShiftRequest> showSRByDID(int depID) {
        ArrayList<ViewShiftRequest> list = new ArrayList<ViewShiftRequest>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowSRByDID(?)}");
            cStmt.setInt(1, depID);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewShiftRequest view = new ViewShiftRequest();
                view.setShiftRequestID(rs.getInt(1));
                view.setEmployeeID(rs.getInt(2));
                view.setCreateDate(rs.getDate(3));
                view.setStartDate(rs.getDate(4));
                view.setEndDate(rs.getDate(5));
                view.setShiftId(rs.getInt(6));
                view.setShiftName(rs.getString(7));
                view.setReason(rs.getString(8));
                view.setStatus(rs.getInt(9));
                view.setModifiedDate(rs.getDate(10));
                view.setEmployeeName(rs.getString(11));
                view.setLevel(rs.getInt(12));
                view.setDepartmentName(rs.getString(13));
                list.add(view);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ShiftRequest> showSRByEID(int userID) {
        ArrayList<ShiftRequest> list = new ArrayList<ShiftRequest>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowShiftRequestByEID(?)}");
            cStmt.setInt(1, userID);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ShiftRequest shiftRequest = new ShiftRequest();
                shiftRequest.setShiftRequestId(rs.getInt(1));
                shiftRequest.setEmployeeId(rs.getInt(2));
                shiftRequest.setCreateDate(rs.getDate(3));
                shiftRequest.setStartDate(rs.getDate(4));
                shiftRequest.setEndDate(rs.getDate(5));
                shiftRequest.setShiftId(rs.getInt(6));
                shiftRequest.setShiftName(rs.getString(7));
                shiftRequest.setReason(rs.getString(8));
                shiftRequest.setStatus(rs.getInt(9));
                shiftRequest.setModifiedDate(rs.getDate(10));
                list.add(shiftRequest);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public boolean insertShiftRequest(ShiftRequest request) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call InsertShiftRequest(?,?,?,?,?)}");
            cStmt.setInt(1, request.getEmployeeId());
            cStmt.setDate(2, new java.sql.Date(request.getStartDate().getTime()));
            cStmt.setDate(3, new java.sql.Date(request.getEndDate().getTime()));
            cStmt.setInt(4, request.getShiftId());
            cStmt.setString(5, request.getReason());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean updateShiftRequest(ShiftRequest request) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call UpdateShiftRequest(?,?,?,?,?)}");
            cStmt.setInt(1, request.getShiftRequestId());
            cStmt.setInt(2, request.getEmployeeId());
            cStmt.setDate(3, new java.sql.Date(request.getStartDate().getTime()));
            cStmt.setDate(4, new java.sql.Date(request.getEndDate().getTime()));
            cStmt.setInt(5, request.getShiftId());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean deleteShiftRequest(ShiftRequest request) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call DeleteShiftRequest(?)}");
            cStmt.setInt(1, request.getShiftRequestId());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean setStatusOfSR(int ID, int status) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call SetStatusOfSR(?,?)}");
            cStmt.setInt(1, ID);
            cStmt.setInt(2, status);
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public ArrayList<Holiday> showHoliday() {
        ArrayList<Holiday> list = new ArrayList<Holiday>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowHoliday()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                Holiday holiday = new Holiday();
                holiday.setHolidayID(rs.getInt(1));
                holiday.setTitle(rs.getString(2));
                holiday.setStartDate(rs.getDate(3));
                holiday.setEndDate(rs.getDate(4));
                holiday.setDescription(rs.getString(5));
                holiday.setModifiedDate(rs.getDate(6));
                list.add(holiday);


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public boolean insertHoliday(Holiday holiday) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call InsertHoliday(?,?,?,?,?)}");
            cStmt.registerOutParameter(1, Types.INTEGER);
            cStmt.setString(2, holiday.getTitle());
            cStmt.setDate(3, new java.sql.Date(holiday.getStartDate().getTime()));
            cStmt.setDate(4, new java.sql.Date(holiday.getEndDate().getTime()));
            cStmt.setString(5, holiday.getDescription());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                int id = cStmt.getInt(1);
                holiday.setHolidayID(id);
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean updateHoliday(Holiday holiday) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call UpdateHoliday(?,?,?,?,?)}");
            cStmt.setInt(1, holiday.getHolidayID());
            cStmt.setString(2, holiday.getTitle());
            cStmt.setDate(3, new java.sql.Date(holiday.getStartDate().getTime()));
            cStmt.setDate(4, new java.sql.Date(holiday.getEndDate().getTime()));
            cStmt.setString(5, holiday.getDescription());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean deleteHoliday(Holiday holiday) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call DeleteHoliday(?)}");
            cStmt.setInt(1, holiday.getHolidayID());
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;


            }
        } catch (SQLException ex) {
            Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public ArrayList<EmployeeDepartmentHistory> showEmployeeDepH() {
        ArrayList<EmployeeDepartmentHistory> list = new ArrayList<EmployeeDepartmentHistory>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowEmployeeDepartmentHistory()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                EmployeeDepartmentHistory record = new EmployeeDepartmentHistory();
                record.setEmployeeID(rs.getInt(1));
                record.setDepartmentID(rs.getInt(2));
                record.setDepartmentName(rs.getString(3));
                record.setShiftID(rs.getInt(4));
                record.setStartDate(rs.getDate(5));
                record.setEndDate(rs.getDate(6));
                record.setModifiedDate(rs.getDate(7));
                list.add(record);
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewScheduler> showViewScheduler() {
        ArrayList<ViewScheduler> list = new ArrayList<ViewScheduler>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowViewScheduler()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewScheduler record = new ViewScheduler();
                record.setEmployeeID(rs.getInt(1));
                record.setLoginID(rs.getString(2));
                record.setName(rs.getString(3));
                record.setDepartmentID(rs.getInt(4));
                record.setDepartmentName(rs.getString(5));
                record.setLevel(rs.getInt(6));
                record.setShiftID(rs.getInt(7));
                record.setShiftName(rs.getString(8));
                record.setStartDate(rs.getDate(9));
                record.setEndDate(rs.getDate(10));
                record.setModifiedDate(rs.getDate(11));
                list.add(record);
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewScheduler> showESchedulerInDep(int eid, int did) {
        ArrayList<ViewScheduler> list = new ArrayList<ViewScheduler>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowESchedulerInDep(?, ?)}");
            cStmt.setInt(1, eid);
            cStmt.setInt(2, did);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewScheduler record = new ViewScheduler();
                record.setEmployeeID(rs.getInt(1));
                record.setLoginID(rs.getString(2));
                record.setName(rs.getString(3));
                record.setDepartmentID(rs.getInt(4));
                record.setDepartmentName(rs.getString(5));
                record.setLevel(rs.getInt(6));
                record.setShiftID(rs.getInt(7));
                record.setShiftName(rs.getString(8));
                record.setStartDate(rs.getDate(9));
                record.setEndDate(rs.getDate(10));
                record.setModifiedDate(rs.getDate(11));
                list.add(record);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewScheduler> showViewHeadScheduler() {
        ArrayList<ViewScheduler> list = new ArrayList<ViewScheduler>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowESchedulerForManager()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewScheduler record = new ViewScheduler();
                record.setEmployeeID(rs.getInt(1));
                record.setLoginID(rs.getString(2));
                record.setName(rs.getString(3));
                record.setDepartmentID(rs.getInt(4));
                record.setDepartmentName(rs.getString(5));
                record.setLevel(rs.getInt(6));
                record.setShiftID(rs.getInt(7));
                record.setShiftName(rs.getString(8));
                record.setStartDate(new Date(rs.getDate(9).getTime()));
                java.sql.Date endDate = rs.getDate(10);
                if (endDate != null) {
                    record.setEndDate(new Date(endDate.getTime()));
                }
                record.setModifiedDate(rs.getDate(11));
                list.add(record);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewScheduler> showViewHeadScheduler(int userid, int depid) {
        ArrayList<ViewScheduler> list = new ArrayList<ViewScheduler>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowHeadSchedulerForHead(?, ?)}");
            cStmt.setInt(1, userid);
            cStmt.setInt(2, depid);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewScheduler record = new ViewScheduler();
                record.setEmployeeID(rs.getInt(1));
                record.setLoginID(rs.getString(2));
                record.setName(rs.getString(3));
                record.setDepartmentID(rs.getInt(4));
                record.setDepartmentName(rs.getString(5));
                record.setLevel(rs.getInt(6));
                record.setShiftID(rs.getInt(7));
                record.setShiftName(rs.getString(8));
                record.setStartDate(new Date(rs.getDate(9).getTime()));
                java.sql.Date endDate = rs.getDate(10);
                if (endDate != null) {
                    record.setEndDate(new Date(endDate.getTime()));
                }
                record.setModifiedDate(rs.getDate(11));
                list.add(record);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewScheduler> showViewEScheduler(int depid) {
        ArrayList<ViewScheduler> list = new ArrayList<ViewScheduler>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowViewESchedulerByDID(?)}");
            cStmt.setInt(1, depid);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewScheduler record = new ViewScheduler();
                record.setEmployeeID(rs.getInt(1));
                record.setLoginID(rs.getString(2));
                record.setName(rs.getString(3));
                record.setDepartmentID(rs.getInt(4));
                record.setDepartmentName(rs.getString(5));
                record.setLevel(rs.getInt(6));
                record.setShiftID(rs.getInt(7));
                record.setShiftName(rs.getString(8));
                record.setStartDate(new Date(rs.getDate(9).getTime()));
                java.sql.Date endDate = rs.getDate(10);
                if (endDate != null) {
                    record.setEndDate(new Date(endDate.getTime()));
                }
                record.setModifiedDate(rs.getDate(11));
                list.add(record);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewScheduler> showViewDoctorScheduler() {
        ArrayList<ViewScheduler> list = new ArrayList<ViewScheduler>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowViewDoctorScheduler()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewScheduler record = new ViewScheduler();
                record.setEmployeeID(rs.getInt(1));
                record.setLoginID(rs.getString(2));
                record.setName(rs.getString(3));
                record.setDepartmentID(rs.getInt(4));
                record.setDepartmentName(rs.getString(5));
                record.setLevel(rs.getInt(6));
                record.setShiftID(rs.getInt(7));
                record.setShiftName(rs.getString(8));
                record.setStartDate(new Date(rs.getDate(9).getTime()));
                java.sql.Date endDate = rs.getDate(10);
                if (endDate != null) {
                    record.setEndDate(new Date(endDate.getTime()));
                }
                record.setModifiedDate(rs.getDate(11));
                list.add(record);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<ViewScheduler> showViewSchedulerByEID(int did) {
        ArrayList<ViewScheduler> list = new ArrayList<ViewScheduler>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowViewDoctorSchedulerByEID(?)}");
            cStmt.setInt(1, did);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                ViewScheduler record = new ViewScheduler();
                record.setEmployeeID(rs.getInt(1));
                record.setLoginID(rs.getString(2));
                record.setName(rs.getString(3));
                record.setDepartmentID(rs.getInt(4));
                record.setDepartmentName(rs.getString(5));
                record.setLevel(rs.getInt(6));
                record.setShiftID(rs.getInt(7));
                record.setShiftName(rs.getString(8));
                record.setStartDate(new Date(rs.getDate(9).getTime()));
                java.sql.Date endDate = rs.getDate(10);
                if (endDate != null) {
                    record.setEndDate(new Date(endDate.getTime()));
                }
                record.setModifiedDate(rs.getDate(11));
                list.add(record);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ViewScheduler showViewSchedulerByPK(int eid, int did, int sid, Date date) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowViewScheduler()}");
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                int employeeID = rs.getInt(1);
                int departmentID = rs.getInt(4);
                int shiftID = rs.getInt(7);
                Date startDate = new Date(rs.getDate(9).getTime());
                if (employeeID == eid && departmentID == did && shiftID == sid
                        && (startDate.getDay() == date.getDay() && startDate.getMonth() == date.getMonth() && startDate.getYear() == date.getYear())) {
                    ViewScheduler record = new ViewScheduler();
                    record.setEmployeeID(employeeID);
                    record.setLoginID(rs.getString(2));
                    record.setName(rs.getString(3));
                    record.setDepartmentID(departmentID);
                    record.setDepartmentName(rs.getString(5));
                    record.setLevel(rs.getInt(6));
                    record.setShiftID(shiftID);
                    record.setShiftName(rs.getString(8));
                    record.setStartDate(startDate);
                    record.setEndDate(rs.getDate(10));
                    record.setModifiedDate(rs.getDate(11));
                    return record;
                }
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return null;
    }

    public ArrayList<EmployeeDepartmentHistory> showEmployeeDepHByEID(int employeeID) {
        ArrayList<EmployeeDepartmentHistory> list = new ArrayList<EmployeeDepartmentHistory>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowEmployeeDepartmentHByEID(?)}");
            cStmt.setInt(1, employeeID);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                EmployeeDepartmentHistory record = new EmployeeDepartmentHistory();
                record.setEmployeeID(rs.getInt(1));
                record.setDepartmentID(rs.getInt(2));
                record.setDepartmentName(rs.getString(3));
                record.setShiftID(rs.getInt(4));
                record.setStartDate(rs.getDate(5));
                record.setEndDate(rs.getDate(6));
                record.setModifiedDate(rs.getDate(7));
                list.add(record);
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public ArrayList<EmployeeDepartmentHistory> showEmployeeDepHByDID(int departmentID) {
        ArrayList<EmployeeDepartmentHistory> list = new ArrayList<EmployeeDepartmentHistory>();
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call ShowEmployeeDepartmentHByDID(?)}");
            cStmt.setInt(1, departmentID);
            ResultSet rs = cStmt.executeQuery();
            while (rs.next()) {
                EmployeeDepartmentHistory record = new EmployeeDepartmentHistory();
                record.setEmployeeID(rs.getInt(1));
                record.setDepartmentID(rs.getInt(2));
                record.setDepartmentName(rs.getString(3));
                record.setShiftID(rs.getInt(4));
                record.setStartDate(rs.getDate(5));
                record.setEndDate(rs.getDate(6));
                record.setModifiedDate(rs.getDate(7));
                list.add(record);
            }
        } catch (Exception e) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return list;
    }

    public boolean insertEmployeeDepH(EmployeeDepartmentHistory record) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call InsertEmployeeDepartmentHistory(?, ?, ?, ?, ?)}");
            cStmt.setInt(1, record.getEmployeeID());
            cStmt.setInt(2, record.getDepartmentID());
            cStmt.setInt(3, record.getShiftID());
            cStmt.setDate(4, new java.sql.Date(record.getStartDate().getTime()));
            Date endDate = record.getEndDate();
            if (endDate != null) {
                cStmt.setDate(5, new java.sql.Date(endDate.getTime()));
            } else {
                cStmt.setDate(5, null);
            }
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (Exception ex) {
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean updateEmployeeDepH(EmployeeDepartmentHistory record) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call UpdateEmployeeHistory(?, ? ,?, ?, ?)}");
            cStmt.setInt(1, record.getEmployeeID());
            cStmt.setInt(2, record.getDepartmentID());
            cStmt.setInt(3, record.getShiftID());
            Date startDate = record.getStartDate();
            Date endDate = record.getEndDate();
            cStmt.setDate(4, new java.sql.Date(startDate.getTime()));
            if (endDate != null) {
                cStmt.setDate(5, new java.sql.Date(endDate.getTime()));
            } else {
                cStmt.setDate(5, null);
            }
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }

    public boolean deleteEmployeeDepH(EmployeeDepartmentHistory record) {
        CallableStatement cStmt = null;
        try {
            cStmt = con.prepareCall("{call DeleteEmployeeHistory(?, ?, ?, ?)}");
            cStmt.setInt(1, record.getEmployeeID());
            cStmt.setInt(2, record.getDepartmentID());
            cStmt.setInt(3, record.getShiftID());
            Date startDate = record.getStartDate();
            if (startDate != null) {
                cStmt.setDate(4, new java.sql.Date(record.getStartDate().getTime()));
            } else {
                cStmt.setDate(4, null);
            }
            int rows = cStmt.executeUpdate();
            if (rows == 1) {
                return true;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (cStmt != null) {
                try {
                    cStmt.close();


                } catch (SQLException ex) {
                    Logger.getLogger(DataManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        return false;
    }
}
